package com.easidea.tim.service.saleorder;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springside.core.service.EntityService;

import com.easidea.tim.dao.base.FlowNoDao;
import com.easidea.tim.dao.commodity.ProductDao;
import com.easidea.tim.dao.customer.CustomerDao;
import com.easidea.tim.dao.inventory.StorageDao;
import com.easidea.tim.dao.saleorder.SaleOrderDao;
import com.easidea.tim.dao.saleorder.SaleOrderItemDao;
import com.easidea.tim.dao.system.orgnization.StaffDAO;
import com.easidea.tim.dto.report.StatByProductDto;
import com.easidea.tim.entity.commodity.Product;
import com.easidea.tim.entity.customer.Customer;
import com.easidea.tim.entity.saleorder.SaleOrder;
import com.easidea.tim.entity.saleorder.SaleOrderItem;
import com.easidea.tim.util.DateUtil;
import com.easidea.tim.util.Page;
import com.easidea.tim.util.TimConstant.FlowNoType;

public class SaleOrderService extends EntityService<SaleOrder, SaleOrderDao> {

	private SaleOrderDao saleOrderDao;

	private SaleOrderItemDao saleOrderItemDao;

	private CustomerDao customerDao;

	private ProductDao productDao;

	private FlowNoDao flowNoDao;

	private StaffDAO staffDao;

	private StorageDao storageDao;

	/**
	 * 分页获取销售订单列表
	 * 
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Page getSaleOrderListByCondition(String creator,
			String saleOrderNum, String customerName, Date startTime,
			Date endTime, String orderType, Page page, String status,
			String order) {
		Criteria ca = createSaleOrderListByConditionCriteria(creator,
				saleOrderNum, customerName, startTime, endTime, orderType,
				status, order);
		ca.setProjection(Projections.count("id"));
		Integer totalNum = (Integer) ca.uniqueResult();
		Criteria ca1 = createSaleOrderListByConditionCriteria(creator,
				saleOrderNum, customerName, startTime, endTime, orderType,
				status, order);
		int startIndex = Page.getStartOfPage(page.getCurrentPageNo(), page
				.getPageSize());
		ca1.setFirstResult(startIndex);
		ca1.setMaxResults(page.getPageSize());
		List<SaleOrder> ls = (List<SaleOrder>) ca1.list();
		Page resultPage = new Page(startIndex, totalNum, page.getPageSize(), ls);
		resultPage.setCurrentPageNo(page.getCurrentPageNo());

		return resultPage;

	}

	@SuppressWarnings("unchecked")
	public Page getSaleOrderListByCreator(String creator, String saleOrderNum,
			String customerName, Date startTime, Date endTime,
			String orderType, Page page, String status, String creatorName,
			String order) {
		Criteria ca = createSaleOrderListByConditionCriteria(creator,
				saleOrderNum, customerName, startTime, endTime, orderType,
				status, order);
		ca.setProjection(Projections.count("id"));
		Integer totalNum = (Integer) ca.uniqueResult();
		Criteria ca1 = createSaleOrderListByCreatorCriteria(creator,
				saleOrderNum, customerName, startTime, endTime, orderType,
				status, creatorName, order);
		int startIndex = Page.getStartOfPage(page.getCurrentPageNo(), page
				.getPageSize());
		ca1.setFirstResult(startIndex);
		ca1.setMaxResults(page.getPageSize());
		List<SaleOrder> ls = (List<SaleOrder>) ca1.list();
		Page resultPage = new Page(startIndex, totalNum, page.getPageSize(), ls);
		resultPage.setCurrentPageNo(page.getCurrentPageNo());

		return resultPage;

	}

	@SuppressWarnings( { "unused", "deprecation", "unchecked" })
	private Criteria createSaleOrderListByCreatorCriteria(String creator,
			String saleOrderNum, String customerName, Date startTime,
			Date endTime, String orderType, String status, String creatorName,
			String order) {
		Criteria ca = createUnDeletedCriteria();
		String[] array = null;
		String narray = "";
		if (!StringUtils.isBlank(creator) && creator != null
				&& (!creator.equals("0"))) {
			List<String> teamList = staffDao
					.getTeamworkIdsByLoginUserId(new Long(creator).longValue());
			ca.add(Restrictions.in("creator", teamList));
		}
		// if(creator !=null && !creator.equals("")){
		// ca.add(Restrictions.ilike("creator", creator,MatchMode.ANYWHERE));
		// }

		if (creatorName != null && !creatorName.equals("")) {
			ca.add(Restrictions.ilike("creator", creatorName,
					MatchMode.ANYWHERE));
		}

		if (saleOrderNum != null && !saleOrderNum.equals("")) {
			ca.add(Restrictions.ilike("saleOrderNum", saleOrderNum,
					MatchMode.ANYWHERE));
		}

		if (status != null && !status.equals("")) {
			ca.add(Restrictions.eq("status", new Integer(status).shortValue()));
		}
		if (customerName != null && !customerName.equals("")) {
			ca.createAlias("customer", "customer");
			ca.add(Restrictions.ilike("customer.name", customerName,
					MatchMode.ANYWHERE));
		}

		if (startTime != null && !startTime.equals("") && endTime != null
				&& !endTime.equals("")) {
			ca.add(Restrictions.between("modifyTime", DateUtil
					.getStartTime(startTime), DateUtil.getEndTime(endTime)));
		}

		if (orderType != null && !orderType.equals("")) {
			ca.add(Restrictions.eq("orderType", orderType));
		}

		if (order.equals("modify")) {
			ca.addOrder(Order.desc("modifyTime"));
		} else {
			ca.addOrder(Order.desc("createTime"));
		}

		return ca;
	}

	@SuppressWarnings( { "unused", "deprecation", "unchecked" })
	private Criteria createSaleOrderListByConditionCriteria(String creator,
			String saleOrderNum, String customerName, Date startTime,
			Date endTime, String orderType, String status, String order) {
		Criteria ca = createUnDeletedCriteria();
		String[] array = null;
		String narray = "";
		if (!StringUtils.isBlank(creator) && creator != null
				&& (!creator.equals("0"))) {
			List<String> teamList = staffDao
					.getTeamworkIdsByLoginUserId(new Long(creator).longValue());
			ca.add(Restrictions.in("creator", teamList));
		}
		// if(creator !=null && !creator.equals("")){
		// ca.add(Restrictions.ilike("creator", creator,MatchMode.ANYWHERE));
		// }

		if (saleOrderNum != null && !saleOrderNum.equals("")) {
			ca.add(Restrictions.ilike("saleOrderNum", saleOrderNum,
					MatchMode.ANYWHERE));
		}

		if (status != null && !status.equals("")) {
			ca.add(Restrictions.eq("status", new Integer(status).shortValue()));
		}
		if (customerName != null && !customerName.equals("")) {
			ca.createAlias("customer", "customer");
			ca.add(Restrictions.ilike("customer.name", customerName,
					MatchMode.ANYWHERE));
		}

		if (startTime != null && !startTime.equals("") && endTime != null
				&& !endTime.equals("")) {
			ca.add(Restrictions.between("modifyTime", DateUtil
					.getStartTime(startTime), DateUtil.getEndTime(endTime)));
		}

		if (orderType != null && !orderType.equals("")) {
			ca.add(Restrictions.eq("orderType", orderType));
		}
		if (order.equals("modify")) {
			ca.addOrder(Order.desc("modifyTime"));
		} else {
			ca.addOrder(Order.desc("createTime"));
		}
		return ca;
	}

	@SuppressWarnings("unchecked")
	public List<SaleOrder> GetSaleOrderList(String creator,
			String saleOrderNum, String customerName, Date startTime,
			Date endTime, String orderType, String status, String order) {
		Criteria ca = this.createSaleOrderListByConditionCriteria(creator,
				saleOrderNum, customerName, startTime, endTime, orderType,
				status, order);
		return ca.list();
	}

	/**
	 * 新增或修改时获取初始化数据列表（客户列表）
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Customer> getInitDataList() {
		return customerDao.GetAllCustomer();

	}

	public List<Product> getAllProducts() {
		return this.productDao.getAllValid();
	}

	/**
	 * 保存订单
	 * 
	 * @param saleOrder
	 * @param items
	 * @return
	 */
	public boolean saveSaleOrder(SaleOrder saleOrder, List<SaleOrderItem> items) {
		String flowNo = "";
		double moneyNum = 0;
		double meterNum = 0;
		double piNum = 0;
		String type = saleOrder.getOrderType();

		if (saleOrder.getOrderType().equals("0")) {
			flowNo = this.flowNoDao.genNextFlowNo(FlowNoType.SALE_ORDER);
		} else if (saleOrder.getOrderType().equals("1")) {
			flowNo = this.flowNoDao.genNextFlowNo(FlowNoType.SAMPLE_ORDER);
		} else if (saleOrder.getOrderType().equals("2")) {
			flowNo = this.flowNoDao.genNextFlowNo(FlowNoType.SALE_ORDER_RETURN);
		} else if (saleOrder.getOrderType().equals("3")) {
			flowNo = this.flowNoDao
					.genNextFlowNo(FlowNoType.SAMPLE_ORDER_RETURN);
		}
		for (SaleOrderItem item : items) {

			meterNum = meterNum
					+ Double.valueOf(item.getTotalMeter()).doubleValue();
			moneyNum = moneyNum
					+ (Double.valueOf(item.getTotalMeter()).doubleValue() * item
							.getMoneyNum());
			piNum = piNum + item.getPiNum();
		}

		saleOrder.setCreateTime(new Date());
		saleOrder.setMoneyNum(moneyNum);
		saleOrder.setMeterNum(meterNum);
		saleOrder.setPiNum(piNum);
		saleOrder.setSaleOrderNum(flowNo);
		saleOrder.setId(null);
		if (type.equals("1") || type.equals("3")) {
			saleOrder.setPiNum(Double.valueOf(0));
		}
		saleOrder.setFlowNo(flowNo);
		// saleOrder.setCreator(saleOrder.getCustomer().getStaff().getName());
		this.saleOrderDao.save(saleOrder);

		for (SaleOrderItem item : items) {
			Product product = productDao.getProductByColorAndName(item
					.getProductName(), item.getProductColor());
			double pi = item.getPiNum();
			if (type.equals("2") && pi > 0) {
				item.setPiNum(pi * -1);
			} else {
				item.setPiNum(pi);
			}

			item.setId(null);
			item.setProduct(product);
			item.setSaleOrder(saleOrder);
			this.saleOrderItemDao.save(item);
			// DueFromSummary summary = saveDueFromSummary(saleOrder);
			// this.saveDueFrom(summary, saleOrder);
		}

		return true;
	}

	/**
	 * 修改订单数据
	 * 
	 * @param saleOrder
	 * @param items
	 * @return
	 */
	public boolean updateSaleOrder(SaleOrder saleOrder,
			List<SaleOrderItem> items) {
		double moneyNum = 0;
		double meterNum = 0;
		double piNum = 0;
		SaleOrder saleOrderTmp = this.get(saleOrder.getId());
		String type = saleOrderTmp.getOrderType();
		for (SaleOrderItem item : items) {

			meterNum = meterNum
					+ Double.valueOf(item.getTotalMeter()).doubleValue();
			moneyNum = moneyNum
					+ (Double.valueOf(item.getTotalMeter()).doubleValue() * item
							.getMoneyNum());
			piNum = piNum + item.getPiNum();
		}
		saleOrderTmp.setModifyTime(new Date());
		saleOrderTmp.setMoneyNum(moneyNum);
		saleOrderTmp.setMeterNum(meterNum);
		saleOrderTmp.setReAddress(saleOrder.getReAddress());
		saleOrderTmp.setReTel(saleOrder.getReTel());
		saleOrderTmp.setRePerson(saleOrder.getRePerson());
		saleOrderTmp.setMemo(saleOrder.getMemo());
		// saleOrder.setPiNum(piNum * returnflag);
		if (type.equals("1") || type.equals("3")) {
			saleOrderTmp.setPiNum(Double.valueOf(0));
		} else if (type.equals("2") && piNum > 0) {
			saleOrderTmp.setPiNum(piNum * -1);
		} else {
			saleOrderTmp.setPiNum(piNum);
		}

		if (type.equalsIgnoreCase("0")) {
			saleOrderTmp.setStatus(new Integer(0).shortValue());
			this.storageDao.cancelChargedStorageByFlowNo(saleOrderTmp
					.getFlowNo());
		} else {
			saleOrderTmp.setStatus(new Integer(2).shortValue());
		}

		this.saleOrderItemDao.deleteItemsByOrderId(saleOrder.getId());

		for (SaleOrderItem item : items) {
			if (!(type.equalsIgnoreCase("1") || type.equalsIgnoreCase("3") || type
					.equalsIgnoreCase("2"))) {
				item.setMeterNum("0");
			}

			Product product = productDao.getProductByColorAndName(item
					.getProductName(), item.getProductColor());
			double pi = item.getPiNum();
			if (type.equals("2") && pi > 0) {
				item.setPiNum(pi * -1);
			} else {
				item.setPiNum(pi);
			}

			item.setId(null);
			item.setProduct(product);
			item.setSaleOrder(saleOrderTmp);
			this.saleOrderItemDao.save(item);
			// DueFromSummary summary = saveDueFromSummary(saleOrder);
			// this.saveDueFrom(summary, saleOrder);
		}
		this.saleOrderDao.merge(saleOrderTmp);
		return true;
	}

	/**
	 * 新增或修改保存销售订单
	 * 
	 * @param dto
	 * @param items
	 * @return
	 * 
	 * @SuppressWarnings("deprecation") public boolean saveOrUpdate(SaleOrder
	 *                                  saleOrder, List<SaleOrderItem> items) {
	 *                                  int flag = 0; String flowNo = ""; double
	 *                                  moneyNum = 0; double meterNum = 0;
	 *                                  double piNum = 0; String type =
	 *                                  saleOrder.getOrderType();
	 * 
	 * if (saleOrder.getOrderType().equals("0")) { flowNo =
	 * this.flowNoDao.genNextFlowNo(FlowNoType.SALE_ORDER); } else if
	 * (saleOrder.getOrderType().equals("1")) { flowNo =
	 * this.flowNoDao.genNextFlowNo(FlowNoType.SAMPLE_ORDER); } else if
	 * (saleOrder.getOrderType().equals("2")) { flowNo =
	 * this.flowNoDao.genNextFlowNo(FlowNoType.SALE_ORDER_RETURN); } else if
	 * (saleOrder.getOrderType().equals("3")) { flowNo = this.flowNoDao
	 * .genNextFlowNo(FlowNoType.SAMPLE_ORDER_RETURN); } for (SaleOrderItem item :
	 * items) {
	 * 
	 * meterNum = meterNum + Double.valueOf(item.getTotalMeter()).doubleValue();
	 * moneyNum = moneyNum + (Double.valueOf(item.getTotalMeter()).doubleValue() *
	 * item .getMoneyNum()); piNum = piNum + item.getPiNum(); }
	 * 
	 * if (saleOrder.getId() == -1) { saleOrder.setCreateTime(new Date());
	 * saleOrder.setMoneyNum(moneyNum); saleOrder.setMeterNum(meterNum);
	 * saleOrder.setPiNum(piNum); saleOrder.setSaleOrderNum(flowNo); if
	 * (type.equals("1") || type.equals("3")) {
	 * saleOrder.setPiNum(Double.valueOf(0)); } saleOrder.setFlowNo(flowNo); //
	 * saleOrder.setCreator(saleOrder.getCustomer().getStaff().getName());
	 * this.saleOrderDao.getHibernateTemplate().save(saleOrder); } else {
	 * saleOrder.setModifyTime(new Date()); saleOrder.setMoneyNum(moneyNum);
	 * saleOrder.setMeterNum(meterNum); // saleOrder.setPiNum(piNum *
	 * returnflag); if (type.equals("1") || type.equals("3")) {
	 * saleOrder.setPiNum(Double.valueOf(0)); } else if (type.equals("2") &&
	 * piNum > 0) { saleOrder.setPiNum(piNum * -1); } else {
	 * saleOrder.setPiNum(piNum); } flag = 1; if (type.equalsIgnoreCase("0")) {
	 * saleOrder.setStatus(new Integer(0).shortValue());
	 * this.storageDao.cancelChargedStorageByFlowNo(saleOrder .getFlowNo()); }
	 * else { saleOrder.setStatus(new Integer(2).shortValue()); }
	 * 
	 * this.saleOrderDao.getHibernateTemplate().update(saleOrder);
	 * this.saleOrderItemDao.deleteItemsByOrderId(saleOrder.getId()); } for
	 * (SaleOrderItem item : items) { if (!(type.equalsIgnoreCase("1") ||
	 * type.equalsIgnoreCase("3") || type .equalsIgnoreCase("2"))) { if (flag ==
	 * 1) { item.setMeterNum("0"); } }
	 * 
	 * Product product = productDao.getProductByColorAndName(item
	 * .getProductName(), item.getProductColor()); double pi = item.getPiNum();
	 * if (type.equals("2") && pi > 0) { item.setPiNum(pi * -1); } else {
	 * item.setPiNum(pi); }
	 * 
	 * item.setProduct(product); item.setSaleOrder(saleOrder);
	 * this.saleOrderItemDao.saveSaleOrderItem(item); // DueFromSummary summary =
	 * saveDueFromSummary(saleOrder); // this.saveDueFrom(summary, saleOrder); }
	 * 
	 * return true; }
	 */

	/**
	 * 填充销售订单
	 * 
	 * @param dto
	 * @param items
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public boolean CompleteUpdate(SaleOrder saleOrder, List<SaleOrderItem> items) {
		double moneyNum = 0;
		double meterNum = 0;
		double piNum = 0;
		this.saleOrderItemDao.deleteItemsByOrderId(saleOrder.getId());
		String array[] = null;
		double returnFlag = 1;

		if (saleOrder.getOrderType().equals("2")) {
			returnFlag = -1;
		} else if (saleOrder.getOrderType().equals("3")) {
			returnFlag = -1;

		}
		for (SaleOrderItem item : items) {
			item.setSaleOrder(saleOrder);
			array = item.getMeterNum().split(",");
			double total = 0;
			for (int i = 0; i < array.length; i++) {
				total = total + Double.parseDouble(array[i]);
			}

			piNum = piNum + item.getPiNum();
			meterNum = meterNum + total;
			moneyNum = moneyNum + (total * item.getMoneyNum());
			Product product = productDao.getProductByColorAndName(item
					.getProductName(), item.getProductColor());
			item.setProduct(product);
			item.setTotalMeter(total * returnFlag);
			item.setId(null);
			this.saleOrderItemDao.save(item);
		}
		SaleOrder tempSaleOrder = this.get(saleOrder.getId());
		tempSaleOrder.setMeterNum(meterNum * returnFlag);
		if (saleOrder.getOrderType().equals("0")) {
			tempSaleOrder.setPiNum(piNum);
		}
		tempSaleOrder.setMoneyNum(Double.valueOf(moneyNum).doubleValue()
				* returnFlag);
		tempSaleOrder.setModifyTime(new Date());
		tempSaleOrder.setStatus(new Integer(2).shortValue());
		this.saleOrderDao.merge(tempSaleOrder);
		return true;
	}

	public boolean UpdateMemo(SaleOrder saleorder, String memo) {
		boolean result = false;
		SaleOrder tempSaleOrder = this.get(saleorder.getId());
		tempSaleOrder.setMemo(memo);
		this.saleOrderDao.merge(tempSaleOrder);
		return result;
	}

	public String isExistProduct(String productname, String color, float price) {
		String result = "0";
		Product product = new Product();
		if (color.equals("r")) {
			product = this.productDao.isExistSame(productname);
		} else {
			product = this.productDao.isExistSame(productname, color);
		}

		boolean validate = this.storageDao.validateSampleStorageLimitaion(
				productname, color);
		if (product != null) {
			result = product.getId().toString();
			if (price < product.getBasicPrice().floatValue() && validate) {
				result = product.getId().toString() + "a";
			} else if (price < product.getBasicPrice().floatValue()
					&& !validate) {
				result = product.getId().toString() + "s";
			} else if (price > product.getBasicPrice().floatValue() && validate) {
				result = product.getId().toString() + "b";
			} else if (product.getBasicPrice() == 0) {
				result = "1";
			}
		}
		return result;

	}

	public String isExistProduct(String productname, String color) {
		String result = "0";
		Product product = this.productDao.isExistSame(productname, color);
		if (product != null) {
			result = product.getId().toString();
		}
		return result;

	}

	/**
	 * 根据id获取销售订单及明细
	 * 
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List getSaleOrderItemsById(Long id) {
		return saleOrderItemDao.getItemsByOrderId(id);
	}

	public boolean uppdateSaleOrder(SaleOrder saleOrder) {
		this.saleOrderDao.merge(saleOrder);
		return true;
	}

	/**
	 * 删除销售订单及明细
	 * 
	 * @param id
	 */
	public String deleteSaleOrder(long id) {
		List<SaleOrderItem> items = this.saleOrderItemDao.getItemsByOrderId(id);
		SaleOrder saleOrder = saleOrderDao.get(id);
		boolean cancelflag = false;
		if (saleOrder != null) {
			if (saleOrder.getStatus() > 1) {
				cancelflag = true;
			}
			// cancelflag = (saleOrder.getStatus() ==
			// Short.valueOf(SaleOrder.order_staus_complete));
		}
		for (SaleOrderItem item : items) {
			item.setDeleted((short) 1);
			item.setModifyTime(new Date());
			if (cancelflag) {
				this.storageDao.cancelChargedStorageByFlowNo(saleOrder
						.getFlowNo());
			}
			this.saleOrderItemDao.getHibernateTemplate().update(item);

		}
		saleOrder.setDeleted((short) 1);
		saleOrder.setModifyTime(new Date());
		this.saleOrderDao.merge(saleOrder);
		// deleteDueFromAndSummary(saleOrder);
		return "success";
	}

	public String updateSaleOrderStatus(long id, int status, String responser) {
		SaleOrder order = this.get(id);
		int update = 3;
		int oldstatus = order.getStatus();
		if (oldstatus == 0) {
			update = 1;
		} else if (oldstatus == 1) {
			update = 2;
		} else if (oldstatus == 2) {
			update = 3;
		}
		// else if (oldstatus == 3) {
		// update = 4;
		// }

		order.setStatus(new Integer(update).shortValue());
		order.setModifyTime(new Date());
		order.setResponer(responser);
		this.saleOrderDao.getHibernateTemplate().update(order);
		return "success";
	}

	public String updateSaleOrderSend(long id, String responser) {
		SaleOrder order = this.get(id);
		order.setStatus(Short.parseShort("3"));
		order.setResponer(responser);
		order.setModifyTime(new Date());
		this.saleOrderDao.getHibernateTemplate().update(order);
		return "success";
	}

	public SaleOrder GetSaleOrderByid(long id) {
		SaleOrder order = this.saleOrderDao.getOrderByOrderId(id).get(0);
		return order;
	}

	public List GetSaleOrderListByid(long id) {
		return this.saleOrderDao.getOrderByOrderId(id);
	}

	/*
	 * 统计 @param startDate @param endDate @param statType(按产品或供应商) @return
	 */
	public List<Object[]> statSaleOrder(String customerName, String staffName,
			Date startDate, Date endDate, String statType) {
		if (statType.equals("product")) {
			return this.saleOrderDao.statSaleOrderByProduct(customerName,
					staffName, startDate, endDate);
		} else {
			return this.saleOrderDao.statSaleOrderByCustomer(customerName,
					staffName, startDate, endDate);
		}

	}

	public List<Object[]> getcustomerDetail(Date startDate, Date endDate,
			String id) {
		return this.saleOrderDao.getcustomerDetail(startDate, endDate, id);
	}

	public List<Object[]> getcustomerDetailSimple(Date startDate, Date endDate,
			String id, String staffName) {
		return this.saleOrderDao.getcustomerDetailSimple(startDate, endDate,
				id, staffName);
	}

	public List<Object[]> getcreatorDetail(Date startDate, Date endDate,
			String id) {
		return this.saleOrderDao.getcreatorDetail(startDate, endDate, id);
	}

	/**
	 * 产品销售统计
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<StatByProductDto> statByProductReport(final String startDate,
			final String endDate) {
		Date sDate = DateUtil.getStartTime(DateUtil.strToDate(startDate));
		Date eDate = DateUtil.getEndTime(DateUtil.strToDate(endDate));

		List<Object[]> rst = this.saleOrderDao.statSaleOrderByProduct("", "",
				sDate, eDate);
		List<StatByProductDto> list = new ArrayList<StatByProductDto>();
		for (Object[] obj : rst) {
			StatByProductDto dto = new StatByProductDto();
			if (obj[0] != null)
				dto.setName((String) obj[0]);
			if (obj[1] != null)
				dto.setColor((String) obj[1]);
			if (obj[2] != null)
				dto.setPiNum(((BigDecimal) obj[2]).doubleValue());
			if (obj[3] != null)
				dto.setMeterNum(((BigDecimal) obj[3]).doubleValue());
			else
				dto.setMeterNum(0d);
			if (obj[4] != null)
				dto.setMoneyNum(((BigDecimal) obj[4]).doubleValue());
			else
				dto.setMoneyNum(0d);
			list.add(dto);
		}

		return list;

	}

	/**
	 * 客户汇总统计
	 * 
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<StatByProductDto> statByCustomerReport(String customerName,
			String staffName, String startDate, String endDate) {
		Date sDate = DateUtil.getStartTime(DateUtil.strToDate(startDate));
		Date eDate = DateUtil.getEndTime(DateUtil.strToDate(endDate));

		List<Object[]> rst = saleOrderDao.statSaleOrderByCustomer(customerName,
				staffName, sDate, eDate);
		List<StatByProductDto> list = new ArrayList<StatByProductDto>();
		for (Object[] obj : rst) {
			StatByProductDto dto = new StatByProductDto();
			if (obj[0] != null)
				dto.setName((String) obj[0]);
			if (obj[1] != null)
				dto.setColor(String.valueOf(obj[1]));
			if (obj[2] != null)
				dto.setPiNum((Double) obj[2]);
			if (obj[3] != null)
				dto.setMeterNum((Double) obj[3]);
			else
				dto.setMeterNum(0d);
			if (obj[4] != null)
				dto.setMoneyNum((Double) obj[4]);
			else
				dto.setMoneyNum(0d);
			list.add(dto);
		}

		return list;
	}

	// @SuppressWarnings("unchecked")
	// public List<StatByProductDto> statByCustomerReport(
	// final String customerName, final String staffName,
	// final String startDate, final String endDate) {
	//
	// String customerSql = "";
	// String staffSql = "";
	// if (!customerName.trim().equals("")) {
	// customerSql = " and po.customer.name like :customername";
	// }
	//
	// if (!staffName.trim().equals("")) {
	// staffSql = " and po.creator =:creator";
	// }
	// final String sql = "select po.customer.name,po.customer.id,sum(po.piNum)
	// as piNum, sum(po.meterNum) as meterNum,sum(po.moneyNum) as moneyNum"
	// + " from "
	// + SaleOrder.class.getName()
	// + " as po where po.status='4' and po.deleted = 0 "+ customerSql+ staffSql
	// + " and po.modifyTime >=:startDate and po.modifyTime <=:endDate group by
	// po.customer.id ";
	// System.out.println(sql);
	// return (List<StatByProductDto>) saleOrderDao.getHibernateTemplate()
	// .execute(new HibernateCallback() {
	// public Object doInHibernate(Session session)
	// throws HibernateException, SQLException {
	// Query query = session.createQuery(sql);
	//
	// Date sDate = DateUtil.getStartTime(DateUtil
	// .strToDate(startDate));
	// Date eDate = DateUtil.getEndTime(DateUtil
	// .strToDate(endDate));
	//
	// query.setString("startDate", DateUtil
	// .formatDateTime(sDate));
	// query.setString("endDate", DateUtil
	// .formatDateTime(eDate));
	// if (!customerName.trim().equals("")) {
	// query.setString("customername", "%" + customerName
	// + "%");
	// }
	//
	// if (!staffName.trim().equals("")) {
	// query.setString("creator", staffName);
	// System.out.println(staffName);
	// }
	// List<Object[]> rst = query.list();
	// List<StatByProductDto> list = new ArrayList<StatByProductDto>();
	// for (Object[] obj : rst) {
	// StatByProductDto dto = new StatByProductDto();
	// if (obj[0] != null)
	// dto.setName((String) obj[0]);
	// if (obj[1] != null)
	// dto.setColor(String.valueOf(obj[1]));
	// if (obj[2] != null)
	// dto.setPiNum((Double) obj[2]);
	// if (obj[3] != null)
	// dto.setMeterNum((Double) obj[3]);
	// else
	// dto.setMeterNum(0d);
	// if (obj[4] != null)
	// dto.setMoneyNum((Double) obj[4]);
	// else
	// dto.setMoneyNum(0d);
	// list.add(dto);
	// }
	//
	// return list;
	// }
	// });
	// }

	/**
	 * 销售月报统计
	 * 
	 * @param year
	 * @param month
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<StatByProductDto> statByProductMonth(final int year,
			final int month) {
		final String sql = "select item.productName,item.productColor,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id  and order.modifyTime >=:startDate and order.modifyTime <=:endDate  group by item.product.id ";

		return (List<StatByProductDto>) saleOrderDao.getHibernateTemplate()
				.execute(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Date startDate = DateUtil.getFirstDayOfMonth(year,
								month);
						Date endDate = DateUtil.getLastDayOfMonth(year, month);
						Query query = session.createQuery(sql);

						// Date sDate = DateUtil.getStartTime(startDate);
						// Date eDate = DateUtil.getEndTime(endDate);
						Date sDate = DateUtil.getStartTime(startDate);
						Date eDate = DateUtil.getEndTime(endDate);
						query.setString("startDate", DateUtil
								.formatDateTime(sDate));
						query.setString("endDate", DateUtil
								.formatDateTime(eDate));
						// query.addScalar("name");
						// query.addScalar("color");
						// query.addScalar("piNum");
						// query.addScalar("meterNum");
						// query.addScalar("moneyNum");
						List<Object[]> rst = query.list();
						List<StatByProductDto> list = new ArrayList<StatByProductDto>();
						for (Object[] obj : rst) {
							StatByProductDto dto = new StatByProductDto();
							if (obj[0] != null)
								dto.setName((String) obj[0]);
							if (obj[1] != null)
								dto.setColor((String) obj[1]);
							if (obj[2] != null)
								dto.setPiNum((Double) obj[2]);
							if (obj[3] != null)
								dto.setMeterNum((Double) obj[3]);
							else
								dto.setMeterNum(0d);
							if (obj[4] != null)
								dto.setMoneyNum((Double) obj[4]);
							else
								dto.setMoneyNum(0d);
							list.add(dto);
						}

						return list;
					}
				});
	}

	/**
	 * 销售员业绩月报统计
	 * 
	 * @param year
	 * @param month
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<StatByProductDto> statByCreatorMonth(final String startDate,
			final String endDate) {
		final String sql = "select order.creator,order.id,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0  and order.status='4' and  order.id = item.saleOrder.id  and order.modifyTime >=:startDate and order.modifyTime <=:endDate  group by order.creator ";

		return (List<StatByProductDto>) saleOrderDao.getHibernateTemplate()
				.execute(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {

						Query query = session.createQuery(sql);
						Date sDate = DateUtil.getStartTime(DateUtil
								.strToDate(startDate));
						Date eDate = DateUtil.getEndTime(DateUtil
								.strToDate(endDate));

						query.setString("startDate", DateUtil
								.formatDateTime(sDate));
						query.setString("endDate", DateUtil
								.formatDateTime(eDate));

						List<Object[]> rst = query.list();
						List<StatByProductDto> list = new ArrayList<StatByProductDto>();
						for (Object[] obj : rst) {
							StatByProductDto dto = new StatByProductDto();
							if (obj[0] != null)
								dto.setName((String) obj[0]);
							if (obj[1] != null)
								dto.setColor(String.valueOf(obj[1]));
							if (obj[2] != null)
								dto.setPiNum((Double) obj[2]);
							if (obj[3] != null)
								dto.setMeterNum((Double) obj[3]);
							else
								dto.setMeterNum(0d);
							if (obj[4] != null)
								dto.setMoneyNum((Double) obj[4]);
							else
								dto.setMoneyNum(0d);
							list.add(dto);
						}

						return list;
					}
				});
	}

	@SuppressWarnings("unchecked")
	public List<StatByProductDto> getProductSummeryByCustomer(String startDate,
			String endDate, String customerid) {

		Date sDate = DateUtil.getStartTime(DateUtil.strToDate(startDate));
		Date eDate = DateUtil.getEndTime(DateUtil.strToDate(endDate));

		List<Object[]> rst = this.saleOrderDao.getcustomerDetail(sDate, eDate,
				customerid);
		List<StatByProductDto> list = new ArrayList<StatByProductDto>();
		for (Object[] obj : rst) {
			StatByProductDto dto = new StatByProductDto();
			if (obj[0] != null)
				dto.setName((String) obj[0]);
			if (obj[1] != null)
				dto.setColor(String.valueOf(obj[1]));
			if (obj[2] != null)
				dto.setPiNum(((BigDecimal) obj[2]).doubleValue());
			if (obj[3] != null)
				dto.setMeterNum(((BigDecimal) obj[3]).doubleValue());
			else
				dto.setMeterNum(0d);
			if (obj[4] != null)
				dto.setMoneyNum(((BigDecimal) obj[4]).doubleValue());
			else
				dto.setMoneyNum(0d);
			list.add(dto);
		}

		return list;

	}

	@SuppressWarnings("unchecked")
	public List<StatByProductDto> getProductSummeryByCreator(
			final String startDate, final String endDate, final String creatorid) {
		final String sql = "select item.productName,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id and order.creator=:creatorid  and order.modifyTime >=:startDate and order.modifyTime <=:endDate  group by item.productName ";

		return (List<StatByProductDto>) saleOrderDao.getHibernateTemplate()
				.execute(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {

						Query query = session.createQuery(sql);

						Date sDate = DateUtil.getStartTime(DateUtil
								.strToDate(startDate));
						Date eDate = DateUtil.getEndTime(DateUtil
								.strToDate(endDate));

						query.setString("startDate", DateUtil
								.formatDateTime(sDate));// DateUtil.formatDateTime(sDate)
						query.setString("endDate", DateUtil
								.formatDateTime(eDate));// DateUtil.formatDateTime(eDate)
						query.setString("creatorid", creatorid);
						// query.addScalar("name");
						// query.addScalar("color");
						// query.addScalar("piNum");
						// query.addScalar("meterNum");
						// query.addScalar("moneyNum");
						List<Object[]> rst = query.list();
						List<StatByProductDto> list = new ArrayList<StatByProductDto>();
						for (Object[] obj : rst) {
							StatByProductDto dto = new StatByProductDto();
							if (obj[0] != null)
								dto.setName((String) obj[0]);
							if (obj[1] != null)
								dto.setPiNum((Double) obj[1]);
							if (obj[2] != null)
								dto.setMeterNum((Double) obj[2]);
							else
								dto.setMeterNum(0d);
							if (obj[3] != null)
								dto.setMoneyNum((Double) obj[3]);
							else
								dto.setMoneyNum(0d);
							list.add(dto);
						}

						return list;
					}
				});
	}

	public List<StatByProductDto> getSimpleProductSummeryByCustomer(
			String startDate, String endDate, String customerid,
			String staffName) {
		List<Object[]> rst = this.saleOrderDao
				.getSimpleProductSummeryByCustomer(customerid, staffName,
						startDate, endDate);
		List<StatByProductDto> list = new ArrayList<StatByProductDto>();
		for (Object[] obj : rst) {
			StatByProductDto dto = new StatByProductDto();
			if (obj[0] != null)
				dto.setName((String) obj[0]);
			if (obj[1] != null)
				dto.setPiNum((Double) obj[1]);
			if (obj[2] != null)
				dto.setMeterNum((Double) obj[2]);
			else
				dto.setMeterNum(0d);
			if (obj[3] != null)
				dto.setMoneyNum((Double) obj[3]);
			else
				dto.setMoneyNum(0d);
			list.add(dto);
		}

		return list;
	}

	@SuppressWarnings("unchecked")
	public List<StatByProductDto> getSimpleProductSummeryByCustomerTest(
			final String startDate, final String endDate,
			final String customerid, final String staffName) {

		String staffSql = "";
		if (!staffName.trim().equals("")) {
			staffSql = " and order.creator=:creator";
		}

		final String sql = "select item.productName,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id and order.customer.id=:customerid"
				+ staffSql
				+ " and order.modifyTime >=:startDate and order.modifyTime <=:endDate and order.orderType='0' and order.orderType='2'  group by item.productName "
				+ "UNION select item.productName,sum(item.piNum) as piNum, sum(item.totalMeter) as totalMeter,sum(item.totalMeter*item.moneyNum) as moneyNum"
				+ " from "
				+ SaleOrderItem.class.getName()
				+ " as item,"
				+ SaleOrder.class.getName()
				+ " as order where  order.deleted = 0 and order.status='4' and  order.id = item.saleOrder.id and order.customer.id=:customerid"
				+ staffSql
				+ " and order.modifyTime >=:startDate and order.modifyTime <=:endDate and order.orderType='1' and order.orderType='3'  group by item.productName";

		return (List<StatByProductDto>) saleOrderDao.getHibernateTemplate()
				.execute(new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query query = session.createQuery(sql);

						Date sDate = DateUtil.getStartTime(DateUtil
								.strToDate(startDate));
						Date eDate = DateUtil.getEndTime(DateUtil
								.strToDate(endDate));

						query.setString("startDate", DateUtil
								.formatDateTime(sDate));// DateUtil.formatDateTime(sDate)
						query.setString("endDate", DateUtil
								.formatDateTime(eDate));// DateUtil.formatDateTime(eDate)
						query.setString("customerid", customerid);
						if (!staffName.trim().equals("")) {
							query.setString("creator", staffName);
						}
						// query.addScalar("name");
						// query.addScalar("color");
						// query.addScalar("piNum");
						// query.addScalar("meterNum");
						// query.addScalar("moneyNum");
						List<Object[]> rst = query.list();
						List<StatByProductDto> list = new ArrayList<StatByProductDto>();
						for (Object[] obj : rst) {
							StatByProductDto dto = new StatByProductDto();
							if (obj[0] != null)
								dto.setName((String) obj[0]);
							if (obj[1] != null)
								dto.setPiNum((Double) obj[1]);
							if (obj[2] != null)
								dto.setMeterNum((Double) obj[2]);
							else
								dto.setMeterNum(0d);
							if (obj[3] != null)
								dto.setMoneyNum((Double) obj[3]);
							else
								dto.setMoneyNum(0d);
							list.add(dto);
						}

						return list;
					}
				});
	}

	/**
	 * 
	 * 
	 * 
	 * public String isCompleteProduct(String productName,String
	 * productColor,String soflowNo,String meter){ String result = "1"; String
	 * meters[]=meter.split(","); Float meterf[] = new Float[meters.length];
	 * //Pattern pattern = Pattern.compile("[0-9]*"); for(int i =0;i<meters.length;i++){
	 * //Matcher isNum = pattern.matcher(meters[i]); //if(!isNum.matches()){ //
	 * result = "1"; // return result; //} meterf[i] = Float.valueOf(meters[i]); }
	 * 
	 * result = storageDao.Incharge(productName, productColor, soflowNo,
	 * meterf); return result; }
	 */
	public List<Object[]> statSaleOrderCreatorByMonth(Date startDate,
			Date endDate) {
		return saleOrderDao.statSaleOrderCreatorByMonth(startDate, endDate);
	}

	public List<Object[]> statSaleOrderProductMonth(int year, int month) {
		return saleOrderDao.statSaleOrderProductByMonth(year, month);
	}

	/**
	 * 根据客户名称、品名、色号，获取对应客户指定品名色号的最近一次销售单价，若无单价
	 * 
	 * @param customerName
	 * @param productName
	 * @param colorCode
	 * @return
	 */
	public List<Object[]> getCustomerProductPrice(String customerName,
			String productName, String colorCode) {
		List<Object[]> rtnList = null;
		if (StringUtils.isNotBlank(customerName)
				&& StringUtils.isNotBlank(productName)) {
			final String sql = "select * from ((SELECT cus.name AS custName, it.productName AS productName, it.productColor AS productColor, so.orderType AS orderType, "
					+ " it.moneyNum AS moneyNum, so.create_time AS createTime "
					+ " FROM tim_salesoder_order so, tim_salesoder_order_item it, tim_customer_customer cus "
					+ " WHERE cus.id = so.customer_id AND it.saleorder_id = so.id "
					+ " AND it.productName = '"
					+ productName.trim()
					+ (StringUtils.isNotBlank(colorCode) ? "' AND it.productColor = '"
							+ colorCode.trim()
							: "")
					+ "' AND cus.name LIKE '%"
					+ customerName.trim()
					+ "%'"
					+ " AND so.orderType = 0 "
					+ " ORDER BY so.create_time  DESC "
					+ " LIMIT 1) "
					+ " UNION "
					+ "(SELECT cus.name AS custName, it.productName AS productName, it.productColor AS productColor, so.orderType AS orderType, "
					+ " it.moneyNum AS moneyNum, so.create_time AS createTime "
					+ " FROM tim_salesoder_order so, tim_salesoder_order_item it, tim_customer_customer cus "
					+ " WHERE cus.id = so.customer_id AND it.saleorder_id = so.id "
					+ " AND it.productName = '"
					+ productName.trim()
					+ (StringUtils.isNotBlank(colorCode) ? "' AND it.productColor = '"
							+ colorCode.trim()
							: "")
					+ "' AND cus.name LIKE '%"
					+ customerName.trim()
					+ "%'"
					+ " AND so.orderType = 1 "
					+ " ORDER BY so.create_time  DESC " + " LIMIT 1)) temp";

			rtnList = (List<Object[]>) saleOrderDao.getHibernateTemplate()
					.execute(new HibernateCallback() {
						public Object doInHibernate(Session session)
								throws HibernateException, SQLException {
							SQLQuery sqlQuery = session.createSQLQuery(sql);
							List<Object[]> list = (List<Object[]>) sqlQuery
									.list();
							return list;
						}
					});
		}
		return rtnList;
	}

	@Override
	public SaleOrderDao getEntityDao() {
		return saleOrderDao;
	}

	public SaleOrderDao getSaleOrderDao() {
		return saleOrderDao;
	}

	public void setSaleOrderDao(SaleOrderDao saleOrderDao) {
		this.saleOrderDao = saleOrderDao;
	}

	public SaleOrderItemDao getSaleOrderItemDao() {
		return saleOrderItemDao;
	}

	public void setSaleOrderItemDao(SaleOrderItemDao saleOrderItemDao) {
		this.saleOrderItemDao = saleOrderItemDao;
	}

	public CustomerDao getCustomerDao() {
		return customerDao;
	}

	public void setCustomerDao(CustomerDao customerDao) {
		this.customerDao = customerDao;
	}

	public ProductDao getProductDao() {
		return productDao;
	}

	public void setProductDao(ProductDao productDao) {
		this.productDao = productDao;
	}

	public FlowNoDao getFlowNoDao() {
		return flowNoDao;
	}

	public void setFlowNoDao(FlowNoDao flowNoDao) {
		this.flowNoDao = flowNoDao;
	}

	public void setStaffDao(StaffDAO staffDao) {
		this.staffDao = staffDao;
	}

	public StorageDao getStorageDao() {
		return storageDao;
	}

	public void setStorageDao(StorageDao storageDao) {
		this.storageDao = storageDao;
	}

	public StaffDAO getStaffDao() {
		return staffDao;
	}

}
